Working with parameters

The CA.Blocks.DataAccess allows you to work directly with SQL Working with the parameters is one of the key defenses against SQL injection attacks. see SQL Injection attacks

Using parameters

Anything that comes in as a parameter to a function should be turned into a SQL parameter for execution. Doing so will protect against SQL injection attacks.

The parameters are provided at a provider level they can be used by simply calling the type.ToSqlParameter(sqlParameterName)

parameters are named at the SQL provider level as @ParameterName Then your code you take the .NET type and call the ToSqlParameter method to assign the .NET parameter to the SQL Parameter.

below is a simple example of adding the "searchTerm" Parameter to the command

    public IList<ProductSummary> GetProductSummaryContainingName(string searchTerm)
    {
        var sql = "Select ProductID, Name, ProductNumber, ReorderPoint, StandardCost, rowguid, ModifiedDate From [Production].[Product] Where Name like @searchTerm";
        var cmd = CreateTextCommand(sql);
        cmd.Parameters.Add(searchTerm.ToSqlParameter("@searchTerm"));
        return Execute(cmd).ToListOf<ProductSummary>();
    }

Given the above, in the SQL we specify the parameters by Name ie "Where Name like @searchTerm". Toto assign a value to "@searchTerm" in the SQL we Do this via the command we take the .NET string value searchTerm and call

    var sqlParameterValue = searchTerm.ToSqlParameter("@searchTerm")

This returns a SqlParameter that can be added to the SQL Command parameter values.

Example 2 using the cmd with WithParameter

    public IList<ProductSummary> GetProductSummaryContainingName(string searchTerm)
    {
        var sql = "Select ProductID, Name, ProductNumber, ReorderPoint, StandardCost, rowguid, ModifiedDate From [Production].[Product] Where Name like @searchTerm";
        var cmd = CreateTextCommand(sql).WithParameter(searchTerm.ToSqlParameter("@searchTerm"));
        return Execute(cmd).ToListOf<ProductSummary>();
    }

Out of the box, the blocks support the base .net value types with null support. So you can call ToSqlParameter on (bool, byte, byte[], Datetime, string, short, int, long, guid, float, decimal, timeSpan, sbyte)